

use MyMediaStore_DW

go

IF OBJECT_ID('dbo.processTree') IS NOT NULL
	DROP PROCEDURE dbo.processTree;

go

create procedure dbo.processTree(@node int)
as
begin

	declare @isRoot bit;
	declare @isLeaf bit;
	declare @employeeid int;
	declare @parentEmployeeid int;
	
	-- verifica de e raiz
	if(not exists( select *
				from [Sales].[Employee]
				where oltp_id = @node and reportsTo_oltp_id <> oltp_id and reportsTo_oltp_id is not null)) set @isRoot = 1;
	else set @isRoot = 0;
	
	-- verifica se e folha
	if(not exists( select * 
				from [Sales].[Employee] as subsidiary
				join [Sales].[Employee] as parentEmp on (subsidiary.reportsTo_oltp_id = parentEmp.oltp_id)
				where parentEmp.oltp_id = @node)) set @isLeaf =  1;
	else set @isLeaf =  0;
	
	select @employeeid = keycol from [Sales].[Employee] where oltp_id = @node
	insert into [Sales].[EmployeeHierarchyBridge] Values(@employeeid, @employeeid, 0, @isLeaf, @isRoot);
	
	-- insere nos pai
	if @isRoot <> 1
	begin
		
		declare @parentNode int;
		set @parentnode = @node;
		
		declare @levelFromParent int;
		set @levelFromParent = 1;
		
		--enquanto tiver parent nodes
		while(exists (
				select reportsTo_oltp_id 
				from [Sales].[Employee] 
				where oltp_id = @parentNode and reportsTo_oltp_id is not null and reportsTo_oltp_id <> oltp_id ))
		begin
			
			-- obtem o no pai
			select @parentNode = reportsTo_oltp_id 
			from [Sales].[Employee] 
			where oltp_id = @parentNode and reportsTo_oltp_id is not null and reportsTo_oltp_id <> oltp_id
			
			select @parentEmployeeid = keycol from [Sales].[Employee] where oltp_id = @parentNode
			insert into [Sales].[EmployeeHierarchyBridge] Values(@parentEmployeeid, @employeeid, @levelFromParent, @isLeaf, @isRoot);
			
			set @levelFromParent = @levelFromParent + 1;
		end
	end
	
	-- processa os filhos
	if @isLeaf <> 1
	begin
	
		declare @childs int;
		declare @childCode int;
		
		select @childs = COUNT(*)
		from [Sales].[Employee] as subsidiary
		join [Sales].[Employee] as parentEmp on (subsidiary.reportsTo_oltp_id = parentEmp.oltp_id)
		where parentEmp.oltp_id = @node;
		
		
		while @childs <> 0
		begin

			select @childCode = employeeid
			from (
				select subsidiary.oltp_id as employeeid, ROW_NUMBER() OVER(ORDER BY subsidiary.oltp_id) as 'rownumber'
				from [Sales].[Employee] as subsidiary
				join [Sales].[Employee] as parentEmp on (subsidiary.reportsTo_oltp_id = parentEmp.oltp_id)
				where parentEmp.oltp_id = @node
			) as t
			where rownumber = @childs;
			
			set @childs = @childs - 1;
			
			exec processTree @childCode ;
		end
	end
end



go

IF OBJECT_ID('dbo.fillBridgeEmployeeTable') IS NOT NULL
	DROP PROCEDURE dbo.fillBridgeEmployeeTable;

go

create procedure fillBridgeEmployeeTable
as
begin
	truncate table [Sales].[EmployeeHierarchyBridge]
	-- obter raiz
	select oltp_id into #bosses from [Sales].[Employee]
	where reportsTo_oltp_id = oltp_id or reportsTo_oltp_id is null
	
	declare @root int;
	
	while exists( select * from #bosses )
	begin
		select @root = oltp_id from #bosses;
		delete from #bosses where oltp_id = @root;
		
		if @root <> -1
			exec processTree @root;
	end
end	

--exec fillBridgeEmployeeTable
--select * from Sales.EmployeeHierarchyBridge order by subsidiaryemployee